﻿using Excel = Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using Office = Microsoft.Office.Core;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Drawing;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.Data.PivotGrid;
using DevExpress.Utils;
using System.Threading;


public partial class UserControl_ControlBaoCaoTangTruongQuy : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Session["db"] != null)
            {
                DataTable db = (DataTable)Session["db"];
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
            }
        }
        else
        {
            ThreadStart thr = new ThreadStart(GetAdnCodeProduct);
            ThreadStart thr1 = new ThreadStart(LoadMonthLy);

            Thread tr = new Thread(thr);
            Thread tr1 = new Thread(thr1);

            tr.Start();
            tr1.Start();
            

            tr.Join();
            tr1.Join();
           
        }
    }
    void ShowMessageBox(string text)
    {
        MessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
    }
    void GetAdnCodeProduct()
    {
        dt = new ClassExecute.ClassExecute();
        if (ViewState["ADNCodeProduct"] == null)
        {
            ViewState["ADNCodeProduct"] = dt.GetDataDepend(str, "k_getADNCodeProduct", CommandType.StoredProcedure, ref error,
                new SqlParameter("@Username", Session["user"].ToString()));
        }
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
           

            DataTable db = dt.Get_Data_DataTable(str,ref error, "k_GrowthQuarter", CommandType.StoredProcedure,
                    new SqlParameter("@userName", Session["user"].ToString()),
                    new SqlParameter("@selYyyyMm",DropQuy.SelectedValue.ToString()),
                    new SqlParameter("@cmpYyyyMm", DropCompare.SelectedValue.ToString()));

           
            if (db.Rows.Count > 0)
            {
                pSelQty.Caption = "S.Lượng";
                pSelAmt.Caption = "D.Số";
                pCmpQty.Caption = "S.Lượng ";
                pCmpAmt.Caption = "D.Số ";

                ASPxPivotGrid1.DataSource = db;

                pSelQty.Caption = pSelQty.Caption + " " + DropQuy.SelectedItem.Text;
                pSelAmt.Caption = pSelAmt.Caption + " " + DropQuy.SelectedItem.Text;
                pCmpQty.Caption = pCmpQty.Caption + " " + DropCompare.SelectedItem.Text;
                pCmpAmt.Caption = pCmpAmt.Caption + " " + DropCompare.SelectedItem.Text;

                Session["db"] = db;
            }
            else
            {
                if (Session["db"] != null)
                {
                    Session["db"] = null;
                }
                ASPxPivotGrid1.DataSource = null;
                ShowMessageBox("Dữ liệu không có");
            }
            ASPxPivotGrid1.DataBind();
            //RadioButtonList1.SelectedValue = "0";
            //ArrangeReport();
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {

                ASPxPivotGridExporter1.OptionsPrint.PrintFilterHeaders = DefaultBoolean.False;
                ASPxPivotGridExporter1.OptionsPrint.PrintColumnHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintRowHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintDataHeaders = DefaultBoolean.False;

                string contentType = "application/ms-excel";

                string fileName = "BaoCaoMucDoTangTruong_" + DropQuy.SelectedValue.ToString() + "_" + DropCompare.SelectedValue.ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                ASPxPivotGridExporter1.ExportToXls(filestream, false);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch
        {

        }
    }
    void LoadMonthLy()
    {
        dt = new ClassExecute.ClassExecute();
        try
        {

            DataTable db = dt.Get_Data_DataTable(str, ref error, "k_getPeriod", CommandType.StoredProcedure);
            dt.BindDropDownList(db,DropQuy, "ID", "Des");
            dt.BindDropDownList(db, DropCompare, "ID", "Des");
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();

                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";

                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
    void generatePivotExcel()
    {

        string fileName = Session["user"].ToString() + "_BaoCaoDoanhSoTangTruongQuy_" + DropQuy.SelectedValue.ToString() + "_" + DropCompare.SelectedValue.ToString() + ".xls";

        if (File.Exists(Server.MapPath(fileName)))
        {
            File.Delete(Server.MapPath(fileName));
        }


        string connection = @"OLEDB;Provider=SQLOLEDB.1;" + str;


        string cmd = "SELECT     * FROM    dml_" + Session["user"].ToString() + "_vwQuaterOffFinal where commodkey in " + ViewState["ADNCodeProduct"].ToString();
        Excel.Application excel = new Excel.Application();
        try
        {
            excel.Visible = false;
            excel.AlertBeforeOverwriting = false;
            object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };
            Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
            style.Font.Bold = true;
            style.Font.Size = 32;
            sheet.Name = "QuarterOffTake_" +DropQuy.SelectedValue.ToString() + "_" + DropCompare.SelectedValue.ToString();
            sheet.Activate();

            foreach (Excel.Worksheet sh in workbook.Worksheets)
            {
                if (sh.Name.ToString() != "QuarterOffTake_" + DropQuy.SelectedValue.ToString() + "_" + DropCompare.SelectedValue.ToString())
                {
                    excel.DisplayAlerts = false;
                    sh.Delete();
                }
            }

            Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

            pivotCache.Connection = connection;
            pivotCache.MaintainConnection = true;




            pivotCache.CommandText = cmd;
            pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
            Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
            Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[16, 2], "PivotTable11", Missing.Value, Missing.Value);



            Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("SiteName");
            pageField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField1.Caption = "Nhà phân phối";
            pageField1.set_Subtotals(1, noSub);

            Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("NVBH");
            pageField4.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField4.Caption = "Nhân viên bán hàng";
            pageField4.set_Subtotals(1, noSub);


            Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("TinhThanh");
            pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField5.Caption = "Tỉnh thành";
            pageField5.set_Subtotals(1, noSub);

            Excel.PivotField pageField6 = (Excel.PivotField)pivotTable.PivotFields("QuanHuyen");
            pageField6.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField6.Caption = "Quận huyện";
            pageField6.set_Subtotals(1, noSub);

            Excel.PivotField pageField7 = (Excel.PivotField)pivotTable.PivotFields("Channel");
            pageField7.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField7.Caption = "Channel";
            pageField7.set_Subtotals(1, noSub);

            Excel.PivotField pageField8 = (Excel.PivotField)pivotTable.PivotFields("SubChannel");
            pageField8.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField8.Caption = "SubChannel";
            pageField8.set_Subtotals(1, noSub);

            Excel.PivotField pageField9 = (Excel.PivotField)pivotTable.PivotFields("Customer");
            pageField9.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField9.Caption = "Khách hàng";
            pageField9.set_Subtotals(1, noSub);

            Excel.PivotField pageField10 = (Excel.PivotField)pivotTable.PivotFields("GAM");
            pageField10.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField10.Caption = "GAM";
            pageField10.set_Subtotals(1, noSub);

            Excel.PivotField pageField11 = (Excel.PivotField)pivotTable.PivotFields("GroupHang");
            pageField11.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField11.Caption = "Group";
            pageField11.set_Subtotals(1, noSub);

            Excel.PivotField pageField12 = (Excel.PivotField)pivotTable.PivotFields("MaHangTheoNPP");
            pageField12.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField12.Caption = "Tên hàng theo NPP";
            pageField12.set_Subtotals(1, noSub);

            Excel.PivotField pageField13 = (Excel.PivotField)pivotTable.PivotFields("MaHangTheoFimex");
            pageField13.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField13.Caption = "Tên hàng theo Fimex";
            pageField13.set_Subtotals(1, noSub);

            Excel.PivotField pageField14 = (Excel.PivotField)pivotTable.PivotFields("LAND");
            pageField14.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField14.Caption = "Vùng";
            pageField14.set_Subtotals(1, noSub);


            //  , , , cmpQty, cmpAmt

           

            pivotTable.AddDataField(pivotTable.PivotFields("cmpQty"), "Số lượng  " + DropCompare.SelectedValue.ToString(), Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("cmpAmt"), "Doanh số  " + DropCompare.SelectedValue.ToString(), Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("selQty"), "Số lượng " + DropQuy.SelectedValue.ToString(), Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("selAmt"), "Doanh số " + DropQuy.SelectedValue.ToString(), Excel.XlConsolidationFunction.xlSum);

           
            ////////////////////
            Excel.CalculatedFields cTTSL = pivotTable.CalculatedFields();
            cTTSL.Add("TTSL", "= iferror(('selQty'-'cmpQty'),0)", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cTTSL, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TTSL"), "TTrưởng S.Lượng", Excel.XlConsolidationFunction.xlSum);

            //////////////////
            Excel.CalculatedFields cTTDS = pivotTable.CalculatedFields();
            cTTDS.Add("TTDS", "= iferror(('selAmt'-'cmpAmt'),0)", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cTTDS, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TTDS"), "TTrưởng D.Số", Excel.XlConsolidationFunction.xlSum);

            //////////////////

            ////////////////////

            Excel.CalculatedFields cQtyRate = pivotTable.CalculatedFields();
            cQtyRate.Add("TangTruongSL", "= iferror(100*('selQty'-'cmpQty')/'cmpQty',0)", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cQtyRate, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TangTruongSL"), "% TTrưởng S.Lượng", Excel.XlConsolidationFunction.xlSum);
            ////////////////
            Excel.CalculatedFields cActRate = pivotTable.CalculatedFields();
            cActRate.Add("TangTruong", "= iferror(100*('selAmt'-'cmpAmt')/'cmpAmt',0)", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cActRate, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TangTruong"), "% TTrưởng D.Số", Excel.XlConsolidationFunction.xlSum);

            ////////////////////
            Excel.CalculatedFields cTTDSL = pivotTable.CalculatedFields();
            cTTDSL.Add("TTDSL", "= iferror(('selQty'-'cmpQty')*('cmpAmt'/'cmpQty'),iferror(('selAmt'-'cmpAmt'),0))", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cTTDSL, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TTDSL"), "TTrưởng do S.Lượng", Excel.XlConsolidationFunction.xlSum);
            ////////////////////
            Excel.CalculatedFields cTTDG = pivotTable.CalculatedFields();
            cTTDG.Add("TTDG", "= iferror((('selAmt'/'selQty')-('cmpAmt'/'cmpQty'))*'selQty',0)", Missing.Value);
            try
            {
                pivotTable.AddDataField((Excel.PivotField)cTTDG, Missing.Value, Missing.Value);
            }
            catch { }
            pivotTable.AddDataField(pivotTable.PivotFields("TTDG"), "TTrưởng do Giá", Excel.XlConsolidationFunction.xlSum);
            ////////////////////

            pivotTable.RowGrand = false;

            pivotTable.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

            Excel.Range rg = pivotTable.DataBodyRange;
            rg.NumberFormat = "#,##0.0#";

            sheet.Cells.EntireColumn.AutoFit();

            Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO MỨC ĐỘ TĂNG TRƯỞNG TỪNG QUÝ";

            Excel.Range range2 = ((Excel.Range)sheet.Cells[2, 1]);
            range2.Value2 = "Quý " + DropQuy.Text.ToString() + " so với " + DropCompare.Text.ToString();

            Excel.Range range1 = ((Excel.Range)sheet.Cells[3, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();



            workbook.SaveAs(Server.MapPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workbook.Close(true, Missing.Value, Missing.Value);
            excel.Quit();

            saveFile(this.Response, Server.MapPath(fileName));

        }
        catch(Exception ex)
        {
            dt = new ClassExecute.ClassExecute();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
        finally
        {
            // Make sure we release the reference to the underlying COM object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
        }
    }
    protected void imgBtnPivot_Click(object sender, ImageClickEventArgs e)
    {
        generatePivotExcel();
    }
    protected void ASPxPivotGrid1_CustomCellDisplayText(object sender, PivotCellDisplayTextEventArgs e)
    {
        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fTTDG"]))
        {

            PivotGridField SalesQty = ASPxPivotGrid1.Fields["pSelQty"];
            object vSalesQty = e.GetCellValue(SalesQty);


            PivotGridField CmpQty = ASPxPivotGrid1.Fields["pCmpQty"];
            object vCmpQty = e.GetCellValue(CmpQty);

            PivotGridField CmpAmt = ASPxPivotGrid1.Fields["pCmpAmt"];
            object vCmpAmt = e.GetCellValue(CmpAmt);

            PivotGridField SalesAmt = ASPxPivotGrid1.Fields["pSelAmt"];
            object vSalesAmt = e.GetCellValue(SalesAmt);

            decimal vResult;
            if ((vSalesQty == null) || ((decimal)vSalesQty == 0) || (vCmpQty == null) || ((decimal)vCmpQty == 0))
            {
                vResult = 0;
            }
            else
            {

                vResult = (((decimal)vSalesAmt / (decimal)vSalesQty) - ((decimal)vCmpAmt / (decimal)vCmpQty)) * (decimal)vSalesQty;

            }
            e.DisplayText = string.Format("{0:###,###.###}", vResult);


        }

        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fTTDSL"]))
        {

            PivotGridField SalesQty = ASPxPivotGrid1.Fields["pSelQty"];
            object vSalesQty = e.GetCellValue(SalesQty);


            PivotGridField CmpQty = ASPxPivotGrid1.Fields["pCmpQty"];
            object vCmpQty = e.GetCellValue(CmpQty);

            PivotGridField CmpAmt = ASPxPivotGrid1.Fields["pCmpAmt"];
            object vCmpAmt = e.GetCellValue(CmpAmt);

            PivotGridField SalesAmt = ASPxPivotGrid1.Fields["pSelAmt"];
            object vSalesAmt = e.GetCellValue(SalesAmt);

            decimal vResult;
            if ((vCmpQty == null) || ((decimal)vCmpQty == 0))
            {
                vResult = (decimal)vSalesAmt;
            }
            else
            {
                vResult = ((decimal)vSalesQty - (decimal)vCmpQty) * ((decimal)vCmpAmt / (decimal)vCmpQty);
            }
            e.DisplayText = string.Format("{0:###,###.###}", vResult);


        }

        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fTTSL"]))
        {
            PivotGridField SalesAmtField = ASPxPivotGrid1.Fields["pSelQty"];
            object SalesAmt = e.GetCellValue(SalesAmtField);

            PivotGridField CmpAmtField = ASPxPivotGrid1.Fields["pCmpQty"];
            object CmpAmt = e.GetCellValue(CmpAmtField);

            if ((SalesAmt == null) || (CmpAmt == null)) return;

            decimal dTTSL;

            dTTSL = (decimal)SalesAmt - (decimal)CmpAmt;
            e.DisplayText = string.Format("{0:###,###.###}", dTTSL);

        }


        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fTTDS"]))
        {
            PivotGridField SalesAmtField = ASPxPivotGrid1.Fields["pSelAmt"];
            object SalesAmt = e.GetCellValue(SalesAmtField);

            PivotGridField CmpAmtField = ASPxPivotGrid1.Fields["pCmpAmt"];
            object CmpAmt = e.GetCellValue(CmpAmtField);

            if ((SalesAmt == null) || (CmpAmt == null)) return;

            decimal dTTDS;

            dTTDS = (decimal)SalesAmt - (decimal)CmpAmt;
            e.DisplayText = string.Format("{0:###,###.###}", dTTDS);

        }

        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fGrowAmt"]))
        {
            PivotGridField SalesAmtField = ASPxPivotGrid1.Fields["pSelAmt"];
            object SalesAmt = e.GetCellValue(SalesAmtField);

            PivotGridField CmpAmtField = ASPxPivotGrid1.Fields["pCmpAmt"];
            object CmpAmt = e.GetCellValue(CmpAmtField);

            if ((SalesAmt == null) || (CmpAmt == null)) return;
            decimal dGrowRate;
            if ((decimal)CmpAmt == 0)
            {
                dGrowRate = 0;
            }
            else
            {
                dGrowRate = decimal.Round(((decimal)SalesAmt - (decimal)CmpAmt) / (decimal)CmpAmt, 4);
            }

            e.DisplayText = string.Format("{0:P}", dGrowRate);

        }
        //////////////////////////////////////////////////////////////////////////////////
        if (object.ReferenceEquals(e.DataField, ASPxPivotGrid1.Fields["fGrowRate"]))
        {
            PivotGridField SalesQtyField = ASPxPivotGrid1.Fields["pSelQty"];
            object SalesQty = e.GetCellValue(SalesQtyField);

            PivotGridField CmpQtyField = ASPxPivotGrid1.Fields["pCmpQty"];
            object CmpQty = e.GetCellValue(CmpQtyField);

            if ((SalesQty == null) || (CmpQty == null)) return;
            decimal dGrowQty;
            if ((decimal)CmpQty == 0)
            {
                dGrowQty = 0;
            }
            else
            {
                dGrowQty = decimal.Round(((decimal)SalesQty - (decimal)CmpQty) / (decimal)CmpQty, 4);
            }

            e.DisplayText = string.Format("{0:P}", dGrowQty);
        }

    }
    protected void imgBtnClearFilter_Click(object sender, ImageClickEventArgs e)
    {
        for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
        {
            ASPxPivotGrid1.Fields[i].FilterValues.Clear();
        }
    }
}